/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     2012/4/3 21:19:22                            */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('T_City') and o.name = 'FK_T_CITY_REFERENCE_T_CITY')
alter table T_City
   drop constraint FK_T_CITY_REFERENCE_T_CITY
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('T_City') and o.name = 'FK_T_CITY_CITYTYPE_T_DICTIO')
alter table T_City
   drop constraint FK_T_CITY_CITYTYPE_T_DICTIO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('T_Goods') and o.name = 'FK_T_GOODS_REFERENCE_T_DICTIO')
alter table T_Goods
   drop constraint FK_T_GOODS_REFERENCE_T_DICTIO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('T_Goods') and o.name = 'FK_T_GOODS_GOODSTYPE_T_GOODST')
alter table T_Goods
   drop constraint FK_T_GOODS_GOODSTYPE_T_GOODST
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('T_GoodsType') and o.name = 'FK_T_GOODST_PARENTGOO_T_GOODST')
alter table T_GoodsType
   drop constraint FK_T_GOODST_PARENTGOO_T_GOODST
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('T_Sale') and o.name = 'FK_T_SALE_FROMSTATI_T_STATIO')
alter table T_Sale
   drop constraint FK_T_SALE_FROMSTATI_T_STATIO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('T_Sale') and o.name = 'FK_T_SALE_TOSTATION_T_STATIO')
alter table T_Sale
   drop constraint FK_T_SALE_TOSTATION_T_STATIO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('T_SaleDetail') and o.name = 'FK_T_SALEDE_REFERENCE_T_SALE')
alter table T_SaleDetail
   drop constraint FK_T_SALEDE_REFERENCE_T_SALE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('T_SaleDetail') and o.name = 'FK_T_SALEDE_REFERENCE_T_DICTIO')
alter table T_SaleDetail
   drop constraint FK_T_SALEDE_REFERENCE_T_DICTIO
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('T_SaleDetail') and o.name = 'FK_T_SALEDE_GOODS_T_GOODS')
alter table T_SaleDetail
   drop constraint FK_T_SALEDE_GOODS_T_GOODS
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('T_Station') and o.name = 'FK_T_STATIO_REFERENCE_T_CITY')
alter table T_Station
   drop constraint FK_T_STATIO_REFERENCE_T_CITY
go

if exists (select 1
            from  sysobjects
           where  id = object_id('V_CityType')
            and   type = 'V')
   drop view V_CityType
go

if exists (select 1
            from  sysobjects
           where  id = object_id('V_Deliveryway')
            and   type = 'V')
   drop view V_Deliveryway
go

if exists (select 1
            from  sysobjects
           where  id = object_id('V_Payway')
            and   type = 'V')
   drop view V_Payway
go

if exists (select 1
            from  sysobjects
           where  id = object_id('V_SaleType')
            and   type = 'V')
   drop view V_SaleType
go

if exists (select 1
            from  sysobjects
           where  id = object_id('V_Shipment')
            and   type = 'V')
   drop view V_Shipment
go

if exists (select 1
            from  sysobjects
           where  id = object_id('V_Unit')
            and   type = 'V')
   drop view V_Unit
go

if exists (select 1
            from  sysobjects
           where  id = object_id('T_City')
            and   type = 'U')
   drop table T_City
go

if exists (select 1
            from  sysobjects
           where  id = object_id('T_Dictionary')
            and   type = 'U')
   drop table T_Dictionary
go

if exists (select 1
            from  sysobjects
           where  id = object_id('T_Goods')
            and   type = 'U')
   drop table T_Goods
go

if exists (select 1
            from  sysobjects
           where  id = object_id('T_GoodsType')
            and   type = 'U')
   drop table T_GoodsType
go

if exists (select 1
            from  sysobjects
           where  id = object_id('T_Operator')
            and   type = 'U')
   drop table T_Operator
go

if exists (select 1
            from  sysobjects
           where  id = object_id('T_Sale')
            and   type = 'U')
   drop table T_Sale
go

if exists (select 1
            from  sysobjects
           where  id = object_id('T_SaleDetail')
            and   type = 'U')
   drop table T_SaleDetail
go

if exists (select 1
            from  sysobjects
           where  id = object_id('T_SaleFee')
            and   type = 'U')
   drop table T_SaleFee
go

if exists (select 1
            from  sysobjects
           where  id = object_id('T_Station')
            and   type = 'U')
   drop table T_Station
go

if exists (select 1
            from  sysobjects
           where  id = object_id('T_Temp_SaleDetail')
            and   type = 'U')
   drop table T_Temp_SaleDetail
go

/*==============================================================*/
/* Table: T_City                                                */
/*==============================================================*/
create table T_City (
   cityID               int                  identity,
   cityName             varchar(100)         null,
   cityCode             varchar(100)         null,
   cityTypeID           int                  null,
   parentCityID         int                  null,
   note                 varchar(500)         null,
   HandlerID            int                  null,
   HandlerTime          datetime             null,
   OperatorID           int                  null,
   OperateTime          datetime             null,
   constraint PK_T_CITY primary key (cityID)
)
go

/*==============================================================*/
/* Table: T_Dictionary                                          */
/*==============================================================*/
create table T_Dictionary (
   dictionaryID         int                  identity,
   dictionaryCode       varchar(100)         null,
   dictionaryName       varchar(100)         null,
   type                 varchar(50)          null,
   cityTypeLevel        int                  null,
   note                 varchar(500)         null,
   HandlerID            int                  null,
   HandlerTime          datetime             null,
   OperatorID           int                  null,
   OperateTime          datetime             null,
   constraint PK_T_DICTIONARY primary key (dictionaryID)
)
go

/*==============================================================*/
/* Table: T_Goods                                               */
/*==============================================================*/
create table T_Goods (
   goodsID              int                  identity,
   goodsTypeID          int                  null,
   goodsName            varchar(100)         null,
   goodsCode            varchar(100)         null,
   isDangerous          bit                  null,
   unitID               int                  null,
   note                 varchar(500)         null,
   HandlerID            int                  null,
   HandlerTime          datetime             null,
   OperatorID           int                  null,
   OperateTime          datetime             null,
   constraint PK_T_GOODS primary key (goodsID)
)
go

/*==============================================================*/
/* Table: T_GoodsType                                           */
/*==============================================================*/
create table T_GoodsType (
   goodsTypeID          int                  identity,
   goodsTypeCode        varchar(50)          null,
   goodsTypeName        varchar(100)         null,
   parentGoodsTypeID    int                  null,
   note                 varchar(500)         null,
   HandlerID            int                  null,
   HandlerTime          datetime             null,
   OperatorID           int                  null,
   OperateTime          datetime             null,
   constraint PK_T_GOODSTYPE primary key (goodsTypeID)
)
go

/*==============================================================*/
/* Table: T_Operator                                            */
/*==============================================================*/
create table T_Operator (
   OperatorID_          int                  identity,
   OperatorName         varchar(100)         null,
   OperatorCode         varchar(100)         null,
   password             varchar(100)         null,
   state                bit                  null,
   note                 varchar(500)         null,
   HandlerID            int                  null,
   HandlerTime          datetime             null,
   OperatorID           int                  null,
   OperateTime          datetime             null,
   constraint PK_T_OPERATOR primary key (OperatorID_),
   constraint AK_UQ_OPERATORCODE_T_OPERAT unique (OperatorCode)
)
go

/*==============================================================*/
/* Table: T_Sale                                                */
/*==============================================================*/
create table T_Sale (
   saleID               int                  identity,
   saleCode             varchar(100)         null,
   saleDate             date                 null,
   status               int                  null,
   customer             varchar(100)         null,
   fromStationID        int                  null,
   fromStationDate      datetime             null,
   fromContact          varchar(100)         null,
   fromContactTelephone varchar(50)          null,
   fromContactAddress   varchar(1000)        null,
   toStationID          int                  null,
   toStationDate        datetime             null,
   toContact            varchar(100)         null,
   toContactTelephone   varchar(50)          null,
   toContactAddress     varchar(1000)        null,
   qtyTotal             float                null,
   grossWeightTotal     float                null,
   weightTotal          float                null,
   volumeTotal          float                null,
   amountTotal          float                null,
   saleTypeID           int                  null,
   paywayID             int                  null,
   shipmentID           int                  null,
   deliverywayID        int                  null,
   note                 varchar(500)         null,
   HandlerID            int                  null,
   HandlerTime          datetime             null,
   OperatorID           int                  null,
   OperateTime          datetime             null,
   constraint PK_T_SALE primary key (saleID)
)
go

/*==============================================================*/
/* Table: T_SaleDetail                                          */
/*==============================================================*/
create table T_SaleDetail (
   saleID               int                  not null,
   goodsID              int                  not null,
   size                 varchar(50)          not null,
   weight               float                null,
   qty                  float                null,
   unitID               int                  null,
   grossWeight          float                null,
   volume               float                null,
   note                 varchar(500)         null,
   HandlerID            int                  null,
   HandlerTime          datetime             null,
   OperatorID           int                  null,
   OperateTime          datetime             null,
   constraint PK_T_SALEDETAIL primary key (saleID, goodsID, size)
)
go

/*==============================================================*/
/* Table: T_SaleFee                                             */
/*==============================================================*/
create table T_SaleFee (
   saleID               int                  not null,
   feeID                int                  identity,
   feeItemID            int                  null,
   amount               float                null,
   note                 varchar(500)         null,
   HandlerID            int                  null,
   HandlerTime          datetime             null,
   OperatorID           int                  null,
   OperateTime          datetime             null,
   constraint PK_T_SALEFEE primary key (saleID, feeID)
)
go

/*==============================================================*/
/* Table: T_Station                                             */
/*==============================================================*/
create table T_Station (
   stationID            int                  identity,
   stationName          varchar(100)         null,
   stationCode          varchar(100)         null,
   cityID               int                  null,
   manager              varchar(50)          null,
   contact              varchar(50)          null,
   telephone            varchar(50)          null,
   address              varchar(1000)        null,
   note                 varchar(500)         null,
   HandlerID            int                  null,
   HandlerTime          datetime             null,
   OperatorID           int                  null,
   OperateTime          datetime             null,
   constraint PK_T_STATION primary key (stationID)
)
go

/*==============================================================*/
/* Table: T_Temp_SaleDetail                                     */
/*==============================================================*/
create table T_Temp_SaleDetail (
   saleID               int                  not null,
   goodsID              int                  not null,
   size                 varchar(50)          not null,
   weight               float                null,
   qty                  float                null,
   unitID               int                  null,
   grossWeight          float                null,
   volume               float                null,
   note                 varbinary(500)       null,
   HandlerID            int                  null,
   HandlerTime          datetime             null,
   OperatorID           int                  null,
   OperateTime          datetime             null,
   constraint PK_T_TEMP_SALEDETAIL primary key (saleID, goodsID, size)
)
go

/*==============================================================*/
/* View: V_CityType                                             */
/*==============================================================*/
create view V_CityType as
select
   dictionaryID cityTypeID,
   dictionaryName cityTypeName,
   dictionaryCode cityTypeCode,
   cityTypeLevel,
   note,
   handlerID,
   handlerTime,
   operatorID,
   operateTime
from
   T_Dictionary
where
   type = 'CityType'
go

/*==============================================================*/
/* View: V_Deliveryway                                          */
/*==============================================================*/
create view V_Deliveryway as
select
   dictionaryID deliverywayID,
   dictionaryName deliverywayName,
   dictionaryCode deliverywayCode,
   note,
   handlerID,
   handlerTime,
   operatorID,
   operateTime
from
   T_Dictionary
where
   type = 'deliverywayID'
go

/*==============================================================*/
/* View: V_Payway                                               */
/*==============================================================*/
create view V_Payway as
select
   dictionaryID paywayID,
   dictionaryName paywayName,
   dictionaryCode paywayCode,
   note,
   handlerID,
   handlerTime,
   operatorID,
   operateTime
from
   T_Dictionary
where
   type = 'payway'
go

/*==============================================================*/
/* View: V_SaleType                                             */
/*==============================================================*/
create view V_SaleType as
select
   dictionaryID saleTypeID,
   dictionaryName saleTypeName,
   dictionaryCode saleTypeCode,
   note,
   handlerID,
   handlerTime,
   operatorID,
   operateTime
from
   T_Dictionary
where
   type = 'saleType'
go

/*==============================================================*/
/* View: V_Shipment                                             */
/*==============================================================*/
create view V_Shipment as
select
   dictionaryID ShipmentID,
   dictionaryName ShipmentName,
   dictionaryCode ShipmentCode
from
   T_Dictionary
where
   type = 'Deliveryway'
go

/*==============================================================*/
/* View: V_Unit                                                 */
/*==============================================================*/
create view V_Unit as
select
   dictionaryID UnitID,
   dictionaryName UnitName,
   dictionaryCode UnitCode
from
   T_Dictionary
where
   type = 'Unit'
go

alter table T_City
   add constraint FK_T_CITY_REFERENCE_T_CITY foreign key (parentCityID)
      references T_City (cityID)
go

alter table T_City
   add constraint FK_T_CITY_CITYTYPE_T_DICTIO foreign key (cityTypeID)
      references T_Dictionary (dictionaryID)
go

alter table T_Goods
   add constraint FK_T_GOODS_REFERENCE_T_DICTIO foreign key (unitID)
      references T_Dictionary (dictionaryID)
go

alter table T_Goods
   add constraint FK_T_GOODS_GOODSTYPE_T_GOODST foreign key (goodsTypeID)
      references T_GoodsType (goodsTypeID)
go

alter table T_GoodsType
   add constraint FK_T_GOODST_PARENTGOO_T_GOODST foreign key (parentGoodsTypeID)
      references T_GoodsType (goodsTypeID)
go

alter table T_Sale
   add constraint FK_T_SALE_FROMSTATI_T_STATIO foreign key (fromStationID)
      references T_Station (stationID)
go

alter table T_Sale
   add constraint FK_T_SALE_TOSTATION_T_STATIO foreign key (toStationID)
      references T_Station (stationID)
go

alter table T_SaleDetail
   add constraint FK_T_SALEDE_REFERENCE_T_SALE foreign key (saleID)
      references T_Sale (saleID)
go

alter table T_SaleDetail
   add constraint FK_T_SALEDE_REFERENCE_T_DICTIO foreign key (unitID)
      references T_Dictionary (dictionaryID)
go

alter table T_SaleDetail
   add constraint FK_T_SALEDE_GOODS_T_GOODS foreign key (goodsID)
      references T_Goods (goodsID)
go

alter table T_Station
   add constraint FK_T_STATIO_REFERENCE_T_CITY foreign key (cityID)
      references T_City (cityID)
go

